﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using DTO;
namespace DAO
{
    public class ThuHoiDAO
    {
        public static void InsertThuHoi(ThuHoiDTO thuHoi)
        {
            String strSql = "insert into ThuHoi(DaiLy, VoPhanPhoi, VoThuVe, SoLuong, PhiDoiVo, MaGiaoHang) values(?, ?, ?, ?, ?, ?)";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@DaiLy", OleDbType.VarWChar);
                com.Parameters.Add("@VoPhanPhoi", OleDbType.VarWChar);
                com.Parameters.Add("@VoThuVe", OleDbType.VarWChar);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@PhiDoiVo", OleDbType.Double);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);

                com.Parameters["@DaiLy"].Value = thuHoi.DaiLy;
                com.Parameters["@VoPhanPhoi"].Value = thuHoi.VoPhanPhoi;
                if (thuHoi.VoThuVe == 0)
                    com.Parameters["@VoThuVe"].Value = System.DBNull.Value;
                else
                    com.Parameters["@VoThuVe"].Value = thuHoi.VoThuVe;
                com.Parameters["@SoLuong"].Value = thuHoi.SoLuong;
                com.Parameters["@PhiDoiVo"].Value = thuHoi.PhiDoiVo;
                com.Parameters["@MaGiaoHang"].Value = thuHoi.MaGiaoHang;
                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                thuHoi.Id = int.Parse(com.ExecuteScalar().ToString());
                if (thuHoi.VoThuVe!=0)
                   VoBinhGasDAO.TangSoLuong(tran, thuHoi.VoThuVe, thuHoi.SoLuong);
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
        }
        public static void InsertThuHoi(OleDbTransaction tran, ThuHoiDTO thuHoi)
        {
            String strSql = "insert into ThuHoi(DaiLy, VoPhanPhoi, VoThuVe, SoLuong, PhiDoiVo, MaGiaoHang) values(?, ?, ?, ?, ?, ?)";
            OleDbConnection conn = DataAccess.OpenOleConnection();            
            try
            {
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@DaiLy", OleDbType.VarWChar);
                com.Parameters.Add("@VoPhanPhoi", OleDbType.VarWChar);
                com.Parameters.Add("@VoThuVe", OleDbType.VarWChar);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@PhiDoiVo", OleDbType.Double);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);

                com.Parameters["@DaiLy"].Value = thuHoi.DaiLy;
                com.Parameters["@VoPhanPhoi"].Value = thuHoi.VoPhanPhoi;
                if (thuHoi.VoThuVe == 0)
                    com.Parameters["@VoThuVe"].Value = System.DBNull.Value;
                else
                    com.Parameters["@VoThuVe"].Value = thuHoi.VoThuVe;
                com.Parameters["@SoLuong"].Value = thuHoi.SoLuong;
                com.Parameters["@PhiDoiVo"].Value = thuHoi.PhiDoiVo;
                com.Parameters["@MaGiaoHang"].Value = thuHoi.MaGiaoHang;
                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                thuHoi.Id = int.Parse(com.ExecuteScalar().ToString());
                if (thuHoi.VoThuVe != 0)
                    VoBinhGasDAO.TangSoLuong(tran, thuHoi.VoThuVe, thuHoi.SoLuong);                
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void UpdateThuHoi(ThuHoiDTO thuHoi)
        {
            
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {
                String strSql = "select VoThuVe, SoLuong from ThuHoi where ID =" + thuHoi.Id;
                DataTable dt = DataAccess.Query(tran, strSql);
                if (dt.Rows[0]["VoThuVe"] != System.DBNull.Value)
                {
                    int loaiGas = (int)dt.Rows[0]["VoThuVe"];
                    int soLuong = (int)dt.Rows[0]["SoLuong"];
                    VoBinhGasDAO.GiamSoLuong(tran, loaiGas, soLuong);
                }
                strSql = "update ThuHoi set DaiLy=?, VoPhanPhoi=?, VoThuVe=?, SoLuong=?, PhiDoiVo=?, MaGiaoHang=? where ID=?";
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;              
               
                com.Parameters.Add("@DaiLy", OleDbType.VarWChar);
                com.Parameters.Add("@VoPhanPhoi", OleDbType.VarWChar);
                com.Parameters.Add("@VoThuVe", OleDbType.VarWChar);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@PhiDoiVo", OleDbType.Double);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@DaiLy"].Value = thuHoi.DaiLy;
                com.Parameters["@VoPhanPhoi"].Value = thuHoi.VoPhanPhoi;
                if (thuHoi.VoThuVe == 0)
                    com.Parameters["@VoThuVe"].Value = System.DBNull.Value;
                else
                    com.Parameters["@VoThuVe"].Value = thuHoi.VoThuVe;
                com.Parameters["@SoLuong"].Value = thuHoi.SoLuong;
                com.Parameters["@PhiDoiVo"].Value = thuHoi.PhiDoiVo;
                com.Parameters["@MaGiaoHang"].Value = thuHoi.MaGiaoHang;
                com.Parameters["@ID"].Value = thuHoi.Id;
                com.ExecuteNonQuery();
                if (thuHoi.VoThuVe != 0)
                VoBinhGasDAO.TangSoLuong(tran,thuHoi.VoThuVe, thuHoi.SoLuong);
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw ex;
            }
        }
        public static void DeleteThuHoi(int id)
        {
            try
            {
                OleDbConnection conn = DataAccess.OpenOleConnection();
                OleDbTransaction tran = conn.BeginTransaction();
                String strSql = "select VoThuVe, SoLuong from ThuHoi where ID=" + id;
                DataTable dt = DataAccess.Query(strSql);
                if (dt.Rows[0]["VoThuVe"] != System.DBNull.Value)
	{
        int loaiGas = (int)dt.Rows[0]["VoThuVe"];
        int soLuong = (int)dt.Rows[0]["SoLuong"];
        VoBinhGasDAO.GiamSoLuong(tran, loaiGas, soLuong);
	}
                
                strSql = "delete from ThuHoi where ID=?";                              
              
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
                tran.Commit();               
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void DeleteThuHoi(OleDbTransaction tran, int id)
        {
            try
            {
                OleDbConnection conn = DataAccess.OpenOleConnection();              
                String strSql = "select VoThuHoi, SoLuong from ThuHoi where ID=" + id;
                DataTable dt = DataAccess.Query(strSql);
                int loaiGas = (int)dt.Rows[0]["VoThuHoi"];
                int soLuong = (int)dt.Rows[0]["SoLuong"];
                VoBinhGasDAO.GiamSoLuong(tran, loaiGas, soLuong);
                strSql = "delete from ThuHoi where ID=?";

                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        }
    }
}
